import sqlite3
import logging
import os
import re
import threading
import sys
from datetime import datetime
from flask import g
from sqlite3 import OperationalError

def resource_path(relative_path):
    """ 获取资源的绝对路径，适用于开发环境和PyInstaller打包后的环境 """
    try:
        if hasattr(sys, '_MEIPASS'):
            # PyInstaller打包后的环境
            if relative_path == 'pms.db':
                base_path = os.path.dirname(sys.executable)
                return os.path.join(base_path, relative_path)
            else:
                return os.path.join(sys._MEIPASS, relative_path)
        else:
            # 开发环境：以 src 上级目录为根
            base_path = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
            return os.path.join(base_path, relative_path)
    except Exception as e:
        logging.error(f"Error in resource_path for '{relative_path}': {e}, using fallback CWD.")
        return os.path.join(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')), relative_path)

class DBUtil:
    """数据库连接工具类"""
    
    def __init__(self, database="pms.db"):
        """初始化数据库连接参数"""
        # 获取数据库的绝对路径
        self.database = resource_path(database)
        self._local = threading.local()  # 使用线程本地存储
        logging.debug(f"数据库路径: {self.database}")
    
    @property
    def connection(self):
        """获取当前线程的连接"""
        if not hasattr(self._local, 'connection') or self._local.connection is None:
            return None
        return self._local.connection
    
    @connection.setter
    def connection(self, conn):
        """设置当前线程的连接"""
        self._local.connection = conn
    
    def connect(self):
        """连接到数据库"""
        try:
            if self.connection is None:
                # 确保每次连接时都获取最新的数据库路径
                self.database = resource_path('pms.db')
                logging.info(f"连接数据库: {self.database}")
                self.connection = sqlite3.connect(self.database)
                # 设置row_factory，使查询结果以字典形式返回
                self.connection.row_factory = lambda cursor, row: {
                    col[0]: row[idx] for idx, col in enumerate(cursor.description)
                }
                self._local.cursor = self.connection.cursor()
                logging.debug("数据库连接成功")
        except Exception as e:
            logging.error(f"数据库连接失败: {str(e)}")
            raise
    
    def close(self):
        """关闭数据库连接"""
        try:
            if self._local.cursor:
                self._local.cursor.close()
            if self.connection:
                self.connection.close()
            self.connection = None
            self._local.cursor = None
            logging.debug("数据库连接已关闭")
        except Exception as e:
            logging.error(f"关闭数据库连接失败: {str(e)}")
            raise
    
    def execute_query(self, sql, params=()):
        """执行查询SQL"""
        try:
            self._local.cursor.execute(sql, params)
            return self._local.cursor.fetchall()
        except Exception as e:
            logging.error(f"执行查询失败: {str(e)}, SQL: {sql}, 参数: {params}")
            raise
    
    def execute_update(self, sql, params=()):
        """执行更新SQL"""
        try:
            self._local.cursor.execute(sql, params)
            self.connection.commit()
            return self._local.cursor.rowcount
        except Exception as e:
            logging.error(f"执行更新失败: {str(e)}, SQL: {sql}, 参数: {params}")
            if self.connection:
                self.connection.rollback()
            raise
    
    def execute_script(self, sql_script):
        """执行多条SQL语句"""
        try:
            if not self.connection:
                self.connect()
            
            # 转换SQL脚本中的占位符
            sql_script = self._convert_placeholders(sql_script)
            
            cursor = self.connection.cursor()
            cursor.executescript(sql_script)
            self.connection.commit()
            cursor.close()
            return True
        except Exception as e:
            if self.connection:
                self.connection.rollback()
            logging.error(f"执行脚本失败: {str(e)}")
            return False
    
    def _clean_sql(self, sql):
        """清理SQL语句，移除注释"""
        lines = []
        for line in sql.split('\n'):
            # 移除SQL注释
            if '--' in line:
                line = line[:line.find('--')]
            if line.strip():
                lines.append(line)
        
        # 替换MySQL特有函数为SQLite函数
        result = ' '.join(lines)
        result = result.replace("NOW()", "datetime('now')")
        return result
    
    def _convert_placeholders(self, sql):
        """将MySQL占位符 %s 转换为SQLite占位符 ?"""
        # 使用正则表达式来替换，避免替换字符串内的 %s
        return re.sub(r'(?<!\')%s(?!\')', '?', sql)
    
    def debug_query(self, sql, params=()):
        """用于调试的查询方法，返回更多信息"""
        try:
            self._local.cursor.execute(sql, params)
            results = self._local.cursor.fetchall()
            return {
                'sql': sql,
                'params': params,
                'row_count': len(results),
                'results': results,
                'success': True
            }
        except Exception as e:
            return {
                'sql': sql,
                'params': params,
                'error': str(e),
                'success': False
            }
    
    def get_tables(self):
        """获取所有表名"""
        try:
            self._local.cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            tables = self._local.cursor.fetchall()
            return [table['name'] for table in tables]
        except Exception as e:
            logging.error(f"获取表名失败: {str(e)}")
            raise
    
    def get_last_insert_id(self):
        """获取最后插入的ID"""
        try:
            if not self.connection:
                return None
            cursor = self.connection.cursor()
            cursor.execute("SELECT last_insert_rowid()")
            result = cursor.fetchone()
            cursor.close()
            if result:
                return result['last_insert_rowid()']
            return None
        except Exception as e:
            logging.error(f"获取最后插入ID失败: {str(e)}")
            return None

    def test_connection(self):
        """测试数据库连接"""
        try:
            self.connect()
            self.close()
            return True
        except:
            return False

# 创建全局单例实例
db_util = DBUtil()

if __name__ == "__main__":
    # 测试数据库连接
    db = DBUtil()
    
    # 测试连接
    print("测试数据库连接...")
    
    if not db.test_connection():
        print("数据库文件不存在或无法连接，将创建新数据库...")
        db.connect()
        db.close()
        print(f"数据库文件 {db.database} 创建成功!")
        
        # 再次测试连接
        if db.test_connection():
            print("数据库连接成功!")
        else:
            print("无法连接到新创建的数据库!")
    else:
        # 如果连接成功，显示数据库中的表
        db.connect()
        tables = db.get_tables()
        print(f"数据库中的表: {tables}")
        
        # 检查是否有数据
        for table in tables:
            if table.startswith('PMS_'):  # 只检查应用相关表
                count = db.execute_query(f"SELECT COUNT(*) as count FROM {table}")
                print(f"表 {table} 中有 {count[0]['count']} 条记录")
        db.close() 